Why Isn’t Oracle Using My Outline / Profile / Baseline?
I seem to have spent a lot of time during the last week having conversations about why Oracle is not locking plans, even when an Outline or SQL Profile has been created. I mean, their whole purpose in life is to keep the optimizer from changing plans, right? Here’s a bit of an email I sent with my thoughts on the issue during a conversation on the Oracle-L list.
First, I think you need to convince yourself as to whether the profile is being used or not. There is a column in v$sql (sql_profile) which will have the name of the profile if one is being used. Also xplan will show that a profile is being used at the bottom of the output.If the profile is being used, but the plan is not what you expected, there are a couple of likely culprits.
First, if it’s a profile generated by SQL Tuning Advisor it’s quite possible that it will have one or more OPT_ESTIMATE hints – which apply scaling factors to various operations. These types of profiles can and do switch plans fairly easily, because they are not even attempting to lock anything. As the stats change, the plans can change.
The second common possibility is that Oracle decided to use an index hint that doesn’t specify the index name, but rather the columns that it would like to use an index on. For example:
INDEX_RS_ASC(@”SEL$1″ “TEST_TABLE”@”SEL$1” (“TEST_TABLE”.”COL2″ “TEST_TABLE”.”TEST_TABLE_ID”))
This hint does not specify an index, but rather columns to use an index on. I’m not sure when this form became a preferred approach but it certainly seems to show up pretty often. So that leaves the optimizer with the flexibility to pick an index based on stats. In systems with lots of indexes, this makes it more likely that a statement will switch plans even though it’s using an Outline or Profile. In fact, you may have a statement with multiple plans in the shared pool, pick one to create an Outline or Profile with, enable it, and have the new child use a plan that’s different from the plan of the child you used to create it with. Very frustrating.
So as I said in the email, the most likely cause for SQL Profiles not working is the non-specific form of index hints that are being used. However, as I was doing some research for this post, I found a couple situations where SQL Profiles just flat don’t work as advertised. I’ll show you an example, but first here are a few scripts that I will use (I sometimes don’t set “echo on” because it makes the examples so messy – so just hit the links to view the scripts):
Updated: 6/14/13
Note that I have rewritten the create_sql_profile.sql script so that it is not dependent on the rg_sqlprof1 script and to add additional functionality. Please use the newer one. Also I have rewritten the sql_profile_hints.sql script so that it works with 10g and 11g. I have disabled the links to the old versions in the list below.
sql_hints.sql – shows the hints stored in the v$sql_plan.other_xml column
sql_profile_hints.sql – shows the hints associated with a SQL Profile
sql_profile_hints11.sql – shows the hints associated with a SQL Profile (for 11g)
create_sql_profile.sql – creates a profile for a statement in the shared pool (wrapper for rg_sqlprof1.sql)
rg_sqlprof1.sql – does the real work to create a profile for a statement in the shared pool
fix_sql_profile_hint.sql – replaces a hint in a profile with whatever you want
Here’s the set up: a simple single table query that wants to do a full table scan and a second version that uses a hint to force an index. A profile created on the hinted statement should keep it from changing, but instead, it changes the plan back to a full table scan. Here’s the example:
> sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jul 30 15:44:02 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @flush_pool
System altered.
SQL> set echo on
SQL> @avgskew
SQL> select avg(pk_col) from kso.skew a where col1 > 0
2 /
AVG(PK_COL)
-----------
16093749.3
SQL> @avgskew_hint2
SQL> select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
2 /
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> -- runs too long, so I ctl-C out
SQL>
SQL> @find_sql
SQL> select sql_id, child_number, plan_hash_value plan_hash, executions execs,
2 (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
3 buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
4 sql_text
5 from v$sql s
6 where upper(sql_text) like upper(nvl('&sql_text',sql_text))
7 and sql_text not like '%from v$sql where sql_text like nvl(%'
8 and sql_id like nvl('&sql_id',sql_id)
9 order by 1, 2, 3
10 /
Enter value for sql_text: %skew%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr 0 568322376 1 11.02 162,436 select avg(pk_col) from kso.skew a where col1 > 0
8js5bhfc668rp 0 3062520228 1 6.84 1,252,591 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
kew a where col1 > 0
SQL> @dplan
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
2 /
Enter value for sql_id: 05cq2hb1r37tr
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05cq2hb1r37tr, child number 0
-------------------------------------
select avg(pk_col) from kso.skew a where col1 > 0
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| SKEW | 1234 | 13574 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1">0)
19 rows selected.
SQL> @dplan
SQL> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','typical'))
2 /
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
Plan hash value: 3062520228
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 28M(100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 1234 | 13574 | 28M (1)| 96:38:01 |
|* 3 | INDEX FULL SCAN | SKEW_COL2_COL1 | 32M| | 120K (1)| 00:24:11 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1">0)
filter("COL1">0)
22 rows selected.
SQL> -- so the unhinted one does a FULL TABLE SCAN
SQL> -- the hinted version uses an index
SQL> -- note that it's doing an INDEX FULL SCAN on SKEW_COL2_COL1
SQL>
SQL> -- let's create a profile on the hinted version - 8js5bhfc668rp
SQL>
SQL> set echo off
SQL> @create_sql_profile
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
Enter value for category:
Enter value for force_matching:
PL/SQL procedure successfully completed.
SQL> @sql_profiles
Enter value for sql_text:
Enter value for name:
NAME CATEGORY STATUS SQL_TEXT FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_8js5bhfc668rp DEFAULT ENABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
SQL> -- now let's execute the statement again
SQL>
SQL> @avgskew_hint2
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
05cq2hb1r37tr 0 568322376 1 11.02 162,436 select avg(pk_col) from kso.skew a where col1 > 0
8js5bhfc668rp 0 568322376 1 6.25 101,709 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
kew a where col1 > 0
SQL> @dplan
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| SKEW | 1234 | 13574 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1">0)
Note
-----
- SQL profile PROFILE_8js5bhfc668rp used for this statement
24 rows selected.
SQL> -- interesting - the statement is now using the profile but the plan changed to a full table scan
SQL> -- this is not what's supposed to happen!
SQL>
SQL> -- let's see what hints got generated for the profile
SQL>
SQL> set echo on
SQL> @sql_profile_hints11
SQL> /* 11g */
SQL>
SQL> set lines 155
SQL> col hint for a150
SQL> select hint from (
2 select p.name, p.signature, p.category,
3 row_number()
4 over (partition by sd.signature, sd.category order by sd.signature) row_num,
5 extractValue(value(t), '/hint') hint
6 from sqlobj$data sd, dba_sql_profiles p,
7 table(xmlsequence(extract(xmltype(sd.comp_data),
8 '/outline_data/hint'))) t
9 where sd.obj_type = 1
10 and p.signature = sd.signature
11 and p.name like nvl('&name',name)
12 )
13 order by row_num
14 /
Enter value for name: PROFILE_8js5bhfc668rp
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
6 rows selected.
SQL>
SQL> -- there are numerous problems with the index hint
SQL> -- 1. the non-specific version of the hint allows the optimizer a lot of flexibility
SQL> -- 2. The hint is attempting INDEX RANGE SCAN, but the plan was INDEX FULL SCAN
SQL> -- 3. All columns in the index are specified, even though the SQL only uses one of the columns
SQL>
SQL> -- let's check it one more time
SQL>
SQL> @drop_sql_profile
SQL> BEGIN
2 DBMS_SQLTUNE.drop_sql_profile (
3 name => '&profile_name',
4 ignore => TRUE);
5 END;
6 /
Enter value for profile_name: PROFILE_8js5bhfc668rp
PL/SQL procedure successfully completed.
SQL> set echo off
SQL> @flush_pool
System altered.
SQL> @avgskew_hint2
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp 0 3062520228 1 3.67 50,061 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
kew a where col1 > 0
SQL> @dplan
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
Plan hash value: 3062520228
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 28M(100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 1234 | 13574 | 28M (1)| 96:38:01 |
|* 3 | INDEX FULL SCAN | SKEW_COL2_COL1 | 32M| | 120K (1)| 00:24:11 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1">0)
filter("COL1">0)
22 rows selected.
SQL> -- back to INDEX FULL SCAN on SKEW_COL2_COL1
SQL>
SQL> -- let's see what hints are attached to the statement in v$sql_plan (so called Outline Hints)
SQL>
SQL> @sql_hints
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
6 rows selected.
SQL> -- so the problem is the outline hints that get created when the statement is parsed
SQL> -- profiles just use what's there
SQL>
SQL> -- let's create the profile again
SQL>
SQL> @create_sql_profile
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
Enter value for category:
Enter value for force_matching:
PL/SQL procedure successfully completed.
SQL> @sql_profiles
Enter value for sql_text:
Enter value for name:
NAME CATEGORY STATUS SQL_TEXT FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_8js5bhfc668rp DEFAULT ENABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
SQL> -- and let's check the hints for the profile again
SQL>
SQL> @sql_profile_hints11
Enter value for name: PROFILE_8js5bhfc668rp
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
6 rows selected.
SQL> -- yep exactly the same as what's in other_xml field of v$sql_plan
SQL>
SQL> -- let's try executing the statement again - should use the profile
SQL>
SQL> @avgskew_hint2
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp 0 568322376 1 4.93 77,126 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
kew a where col1 > 0
SQL> @dplan
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| SKEW | 1234 | 13574 | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1">0)
Note
-----
- SQL profile PROFILE_8js5bhfc668rp used for this statement
24 rows selected.
SQL> -- yep, it is using the profile but not using the plan that the statement was using before we created the profile
SQL>
SQL> -- BAD OPTIMZER! creating a profile on a statement in the shared pool should not change it's plan!
So let’s see if we can work around the problem. To do that, all we need to do is replace the bad index hint (or hints) with one that will work. In this case we’ll probably need to avoid the INDEX_RS_ASC hint because it probably won’t work since we’re not specifying the leading part of the index. Here’s an example:
SQL> -- let's see if we can fix it
SQL>
SQL> @sql_profiles
Enter value for sql_text:
Enter value for name:
NAME CATEGORY STATUS SQL_TEXT FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROFILE_8js5bhfc668rp DEFAULT ENABLED select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a wher NO
SQL> @sql_profile_hints11
Enter value for name: PROFILE_8js5bhfc668rp
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
6 rows selected.
SQL> -- let's replace the bad hint using fix_sql_profile_hints.sql
SQL>
SQL> -- but first, let's see what options are available for index hints
SQL> -- (v$sql_hint shows up in 11g - nice new view)
SQL>
SQL> @valid_hints
SQL> select name,version from v$sql_hint
2 where upper(name) like '%'||upper(nvl('&hint',name))||'%'
3 order by name;
Enter value for hint: INDEX
NAME VERSION
---------------------------------------------------------------- -------------------------
DOMAIN_INDEX_FILTER 11.1.0.6
DOMAIN_INDEX_NO_SORT 8.1.5
DOMAIN_INDEX_SORT 8.1.5
INDEX 8.0.0
INDEX_ASC 8.1.0
INDEX_COMBINE 8.1.0
INDEX_DESC 8.1.0
INDEX_FFS 8.1.0
INDEX_JOIN 8.1.5
INDEX_RRS 9.0.0
INDEX_RS_ASC 11.1.0.6
INDEX_RS_DESC 11.1.0.6
INDEX_SS 9.0.0
INDEX_SS_ASC 9.0.0
INDEX_SS_DESC 9.0.0
INDEX_STATS 10.1.0.3
LOCAL_INDEXES 9.0.0
NO_DOMAIN_INDEX_FILTER 11.1.0.6
NO_INDEX 8.1.5
NO_INDEX_FFS 10.1.0.3
NO_INDEX_SS 10.1.0.3
NO_PARALLEL_INDEX 8.1.0
NO_USE_INVISIBLE_INDEXES 11.1.0.6
NO_XMLINDEX_REWRITE 11.1.0.6
NO_XMLINDEX_REWRITE_IN_SELECT 11.1.0.6
NUM_INDEX_KEYS 10.2.0.3
PARALLEL_INDEX 8.1.0
USE_INVISIBLE_INDEXES 11.1.0.6
USE_NL_WITH_INDEX 10.1.0.3
XMLINDEX_REWRITE 11.1.0.6
XMLINDEX_REWRITE_IN_SELECT 11.1.0.6
31 rows selected.
SQL> -- so there doesn't appear to be a hint that forces an INDEX FULL SCAN (although INDEX_FFS is close)
SQL> -- let's just go with the old fashioned INDEX hint
SQL>
SQL> @fix_sql_profile_hint
Enter value for profile_name: PROFILE_8js5bhfc668rp
Enter value for bad_hint: INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
Enter value for good_hint: INDEX(@"SEL$1" "A"@"SEL$1" "SKEW_COL2_COL1")
version: 11
PL/SQL procedure successfully completed.
SQL> @sql_profile_hints11
Enter value for name: PROFILE_8js5bhfc668rp
HINT
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "A"@"SEL$1" "SKEW_COL2_COL1")
6 rows selected.
SQL> -- looks better, let's test it
SQL> @flush_pool
System altered.
SQL> @avgskew_hint2
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where col1 > 0
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
SQL> @find_sql
Enter value for sql_text: %skew%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
8js5bhfc668rp 0 3062520228 1 3.32 37,091 select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.s
kew a where col1 > 0
SQL> @dplan
Enter value for sql_id: 8js5bhfc668rp
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8js5bhfc668rp, child number 0
-------------------------------------
select /*+ index(a SKEW_COL2_COL1) */ avg(pk_col) from kso.skew a where
col1 > 0
Plan hash value: 3062520228
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 28M(100)| |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 1234 | 13574 | 28M (1)| 96:38:01 |
|* 3 | INDEX FULL SCAN | SKEW_COL2_COL1 | 32M| | 120K (1)| 00:24:11 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1">0)
filter("COL1">0)
Note
-----
- SQL profile PROFILE_8js5bhfc668rp used for this statement
26 rows selected.
SQL> -- yep that works!
SQL>
SQL> -- note - the syntax is very demanding (recommend - INDEX( + cut and paste query block id part + "INDEX_NAME")
So a few closing comments:
- This particular example behaved consistently from 10.2.0.3 through 11.1.0.7 (that is to say, it doesn’t work in any of those versions, but it behaves in a consistent manner).
- This particular example behaved the same for Outlines as it did for SQL Profiles.
- I believe that the best replacement for the INDEX_XXX (table_name.column_name) hint is the simple INDEX(alias index_name) hint.
- The syntax of these hints is very particular, so the best approach in my opinion is to keep all the query block and alias bits and simply replace the (table_name.column_name) bit with the index_name.
- Remember that hints don’t give any error messages, they are just silently ignored if something isn’t right (that’s always been a bit frustrating, seems like there ought to be a switch that that could be set which would allow an error to be thrown if a hint has invalid syntax).
- Outlines, SQL Profiles, Baselines (and Hints themselves for that matter) are a bit flakey (partly because they are not well understood due to the small amount of documentation, and partly because of the lack of error messages when hints are poorly formed). So you should always verify what they are doing by looking at the hints that are being applied. 10053 traces can help – there is a section at the very bottom of the trace file where it indicates if any hints have generated errors. This included hints applied via Outlines or Profiles.
- It’s clear that a syntax error on one of the hints in a Profile will not invalidate the whole Profile. The optimizer will attempt to honor any hints that are valid. (if you’re interested, you can play with the fix_sql_profile_hint.sql script to verify this for yourself)
- All these options are extreme measures that allow manipulation of execution plans when you can’t touch the actual SQL statement. While they can provide very dramatic results in a very short time frame, generally speaking, I do not consider them to be the best long term solution.
As always, your comments are welcome.
I like the idea of an “enhancement” to the Oracle code base so that it could somehow be determined if a hint is syntactically correct. What are the chances Oracle would act on such an enhancement request, in your opinion? On your last point – it has always been frustrating to me, especially with the amount of time it takes to devise a set of hints, or outlines or profiles, that the results are not gauranteed long term (of course, nothing is). Perhaps, pretty soon, when Oracle owns the major code base for every third party app, this will no longer be an issue (ha ha)!
Another reason for ignored profiles/outlines, etc. that I found recently which I had never seen (or at least never knew I had seen) was a differing bind type. Our java developers used a java.util.Date() object rather than a java.sql.Date(). The database took it as a timestamp, and wouldn’t use the index on the DATE column (whose datatype was DATE).
As soon as the changed their code, we didn’t need the profile anymore. Go figure…
Michael,
I have no idea what the odds would be that Oracle would implement such an enhancement request. I’m sure they have an evaluation process they go through, but I don’t have any insight into the process.
Kerry
Steve,
That’s a classic. I had that exact situation about a year ago. 1.5 billion row table, all queries by date range, using java date variable that caused Oracle not to be able to use the index. Actually it was more tricky than that. Because the index had multiple columns, the plan actually used the index, but only on the much less selective other columns. So the index showed up in the plan, but didn’t work very well. They hadn’t noticed that the date was being used as a filter as opposed to an access method.
I’d be interested to hear more details on the steps you went through to create the Outline (or Profile) and then how it behaved. Any details you can share?
Kerry
Hi Kerry,
This was my issue on Oracle-L 🙂 I just read your post now. Thanks a lot for posting the scenario!
Now I clearly understand what you meant by: “Oracle decided to use an index hint that doesn’t specify the index name, but rather the columns that it would like to use an index on”
Well, next time I have more options to play with 🙂
Karl,
Glad to hear your comment. Our discussion on Oracle-L was definitely one of the reasons for the post.
Kerry
And also.. lately.. I’ve been saying a lot of this..
“BAD OPTIMIZER! will ya let me sleep” .. 🙂
[…] Osborne wants to know, Why isn’t Oracle using my outline/profile/baseline? “I seem to have spent a lot of time during the last week having conversations about why […]
[…] Kerry Osborne -Why Isn’t Oracle Using My Outline / Profile / Baseline? […]
[…] Whitepapers / Presentations « Creating Test Scripts With Bind Variables Why Isn’t Oracle Using My Outline / Profile / Baseline? […]
[…] often necessary to fix one or more of the hints (see this post for the most common reason: Why Isn’t Oracle Using My Outline / Profile / Baseline?). The preceding steps allow the Profile to be tweaked before attaching it to the target statement. […]
[…] on the change Oracle made to their Hint based mechanisms (Outlines/Profiles/Baselines) in 10g here: Why Isn’t Oracle Using My Outline / Profile / Baseline. To quickly recap, prior to 10g, the design goal for Outlines appears to have been to […]
Kerry,
I’d like to suggest a different interpretation of your results.
a) A profile and an outline are different things – roughly speaking an outline lists actions and strategies, a profile supplies statistical corrections to the optimizer.
b) The code you’ve used to “create a profile from the shared pool” doesn’t, it copies an outline from the shared pool but tells the optimizer it’s a profile.
c) When you run (or explain) the query with the “profile” in place, the optimizer detects the profile and disables the embedded hints (i.e. the ones in the actual text) and tries to derive an execution plan based on what ought to be statistical content in the profile combined with the basic object stats. But the profile supplies no statistical information.
d) In the absence of statistical information, and after disabling the embedded hints, the optimizer has “used” the profile but produces the original, unhinted, tablescan plan.
Regards
Jonathan Lewis
Jonathan,
Wow – I had to think about this one for a while (and go back to check my work) – here’s my thoughts:
a) A profile and an outline are different things – roughly speaking an outline lists actions and strategies, a profile supplies statistical corrections to the optimizer.
I see Outlines and Profiles (and Baselines for that matter) as basically the same thing. They apply a set of hints to a statement. The standard (documented) way of generating a SQL Profile is to use the the SQL Tuning Advisor – which does calculate scaling factors to apply to various steps and implements them via the OPT_ESTIMATE, TABLE_STATS, COLUMN_STATS hints (and occasionally it adds some strategic type hints…). But Profiles are nevertheless just applying hints (I think).
b) The code you’ve used to “create a profile from the shared pool” doesn’t, it copies an outline from the shared pool but tells the optimizer it’s a profile.
That comment made me laugh – you’re absolutely right of course! But just to be clear to anyone else that might stumble across this dialog, every plan has a set of hints that Oracle thinks will recreate the plan (stored in the other_xml column of v$sql_plan). These hints are the exact hints that are used when you create an Outline. The code in my create_sql_profile.sql script does exactly what you said, it creates a SQL Profile using that same set of hints that would be in the Outline if you created one for the statement.
c) When you run (or explain) the query with the “profile” in place, the optimizer detects the profile and disables the embedded hints (i.e. the ones in the actual text) and tries to derive an execution plan based on what ought to be statistical content in the profile combined with the basic object stats. But the profile supplies no statistical information.
I believe SQL Profiles apply the hints. I don’t think they expect any specific statistical information (i.e. I don’t think they care whether there are any OPT_ESTIMATE, TABLE_STATS, or COLUMN_STATS hints or not) I could be wrong, but I haven’t seen any situation where a Profile was ignored because it didn’t have statistical information.
As a side note, I don’t think the SQL Profiles that are created by the SQL Tuning Advisor disable embedded hints the way Outlines do. I say this because Outline hints include the IGNORE_OPTIM_EMBEDDED_HINTS hint and SQL Profiles created by the Tuning Advisor do not. And if you think about it, that makes sense. Since the latter are basically only supplying statistical information, disabling embedded hints would not make sense. (Note: I have not done a specific test to prove this to myself)
d) In the absence of statistical information, and after disabling the embedded hints, the optimizer has “used” the profile but produces the original, unhinted, tablescan plan.
I came up with a little test case. See if you think this is a valid test.
1. create a SQL Profile (based on hints from other_xml)
2. check hints – there will be no statistical hints
3. manually modify one of the hints in a way that forces a different plan
4. see if it works
If the plan changes as expected, despite the absence of any statistical hints, then that implies to me that Profiles just apply whatever hints are there. Thoughts?
Kerry
Kerry,
a) Outlines vs. profiles: I think these can be viewed on two levels. On one hand they are both represented by a set of hints; on the other hand they exist for different reasons. In principle an outline fixes an execution plan so that it can’t change whereas a profile fixes the optimizer’s understanding of the data distribution pattern so that it can still pick the right plan as the data size varies.
(Other than optimizer_features_enable() and ignore_optim_embedded_hints, I’ve not seen anything but statistical hints in a profile generated by the tuning tool – do you have any examples to demonstrate otherwise ?).
b) One aspect of the difference between outlines and profiles is that the profiling information doesn’t get into the outline when the statement runs. A statement with a profile can produce many different execution plans – which means many different outlines: which, I’d say, is an argument against copying an outline into the profile table.
c) disabling embedded hints – I think I was wrong, but I’m not 100% sure. I’ve just run up an example in 11.1.0.6 to demonstrate the point – and realised that the profile included the ‘optim_ignore_embedded_hints’ hint, so perhaps there are cases where this isn’t included and embedded hints are still obeyed. (In fact, since you were copying from the outline, you automatically copied in the optim_ignore_embedded_hints hint anyway – and then the index_rs_asc() hint was ignored because it is invalid in this context (as you suggested), and shouldn’t have been genereated by the optimizer).
d) That’s a valid test that the code for applying profiles doesn’t care what type of hint you’ve put in the table – and it certainly seemed to be true for a couple of examples I’ve just created. It certainly makes sense – especially in view of the fact that sql-baselines are written into the same table in 11g (Why have two different pieces of code when one will do ?)
Regards
Jonathan Lewis
Jonathan,
a) I agree that Profiles appear to have been built with statistical modifications in mind. It looks to me like they “borrowed” the code from Outlines though. Adding the ability to do the “force matching” thing that let’s them apply to multiple statements that only differ due to literals. My experience with the Profiles generated by SQL Tuning Advisor has not been very good in terms of plan stability though. It does not appear that they have any concept of changes to stats and just apply their scaling to what ever the optimizer comes up with. So if (I should say when) the stats change, the scaling factors may not be correct anymore. I can see them working well for situations where the stats gathering is managed tightly and the optimizer just has no way to deal with a particular situation (like correlated columns or example), but for the general case, they seem to “sour” over time due to changing stats.
Yes, I have seen other hints. We have a client that has created many Profiles with the Tuning Advisor (they are in the process of getting rid of them by the way, due to this tendency to “sour” over time). But back to question about additional hints, I just did a quick review of about 40 Profiles and the vast majority have only statistical hints (OPT_ESTIMATE, COLUMN_STATS, TABLE_STATS, INDEX_STATS). A few have some variation of the (ALL_ROWS, FIRST_ROWS(X)) hint and most have the OPTIMIZER_FEATURES_ENABLE hint as well. To my surprise, I found a couple that had IGNORE_OPTIM_EMBEDDED_HINTS as you were expecting. Even more surprising was that one of the statements had no embedded hints while the other did. (something else to look at I guess) I did find one that had a whole slew of hints. Again, I’m not sure why occasionally we get ones that look like this, but at any rate here’s the list of hints:
OPT_ESTIMATE(@”SEL$5DA710D3″, JOIN, (“E”@”SEL$1”, “B”@”SEL$1”, “A”@”SEL$1”, “X”@”SEL$2″), SCALE_ROWS=135.982493)
IGNORE_OPTIM_EMBEDDED_HINTS
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“F”@”SEL$1”, “B”@”SEL$1″), SCALE_ROWS=26.34181566)
OPTIMIZER_FEATURES_ENABLE(‘10.2.0.4’)
OPT_ESTIMATE(@”SEL$5DA710D3”, INDEX_FILTER, “F”@”SEL$1″, IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)
OPT_PARAM(‘optimizer_index_cost_adj’ 80)
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“D”@”SEL$1”, “C”@”SEL$1”, “A”@”SEL$1”, “X”@”SEL$2″), SCALE_ROWS=1.308307653)
OPT_PARAM(‘optimizer_index_caching’ 60)
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“E”@”SEL$1”, “D”@”SEL$1”, “C”@”SEL$1”, “B”@”SEL$1”, “A”@”SEL$1”, “Y”@”SEL$2″), SCALE_ROWS=862.9359462)
ALL_ROWS
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“E”@”SEL$1”, “A”@”SEL$1″), SCALE_ROWS=25.60960842)
OUTLINE_LEAF(@”SEL$5DA710D3″)
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“C”@”SEL$1”, “A”@”SEL$1″), SCALE_ROWS=7.884506683)
UNNEST(@”SEL$2″)
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“F”@”SEL$1”, “B”@”SEL$1”, “A”@”SEL$1″), SCALE_ROWS=839.9683673)
OUTLINE(@”SEL$1″)
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“B”@”SEL$1”, “A”@”SEL$1″), SCALE_ROWS=4.446153275)
OUTLINE(@”SEL$2″)
OPT_ESTIMATE(@”SEL$5DA710D3”, TABLE, “D”@”SEL$1″, SCALE_ROWS=11.39782103)
FULL(@”SEL$5DA710D3” “A”@”SEL$1″)
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“E”@”SEL$1”, “D”@”SEL$1”, “C”@”SEL$1”, “A”@”SEL$1″), SCALE_ROWS=259.4309108)
FULL(@”SEL$5DA710D3” “B”@”SEL$1″)
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“E”@”SEL$1”, “C”@”SEL$1”, “A”@”SEL$1″), SCALE_ROWS=190.2944942)
FULL(@”SEL$5DA710D3” “X”@”SEL$2″)
OPT_ESTIMATE(@”SEL$5DA710D3”, JOIN, (“E”@”SEL$1”, “C”@”SEL$1”, “A”@”SEL$1”, “X”@”SEL$2″), SCALE_ROWS=26.52093258)
INDEX(@”SEL$5DA710D3” “F”@”SEL$1” (“…”))
INDEX_RS_ASC(@”SEL$5DA710D3″ “C”@”SEL$1” (“…”))
OPT_ESTIMATE(@”SEL$5DA710D3″, INDEX_SKIP_SCAN, “F”@”SEL$1″, IDX$$_1AA260002, SCALE_ROWS=8.883203639e-06)
INDEX_RS_ASC(@”SEL$5DA710D3” “E”@”SEL$1” (“…”))
ALL_ROWS
INDEX(@”SEL$5DA710D3″ “D”@”SEL$1” (“…”))
LEADING(@”SEL$5DA710D3″ “A”@”SEL$1” “B”@”SEL$1” “X”@”SEL$2” “F”@”SEL$1” “C”@”SEL$1” “E”@”SEL$1” “D”@”SEL$1″)
USE_HASH(@”SEL$5DA710D3” “B”@”SEL$1″)
USE_HASH(@”SEL$5DA710D3” “X”@”SEL$2″)
USE_NL(@”SEL$5DA710D3” “F”@”SEL$1″)
USE_NL(@”SEL$5DA710D3” “C”@”SEL$1″)
USE_NL(@”SEL$5DA710D3” “E”@”SEL$1″)
USE_NL(@”SEL$5DA710D3” “D”@”SEL$1″)
SWAP_JOIN_INPUTS(@”SEL$5DA710D3” “X”@”SEL$2”)
b) Agreed. I am using Profiles in a way that the designers may not have intended. The argument for using Profiles in this manner would be that Profiles can match multiple statements that differ only by literals, while Outlines can’t. Profiles are a little cleaner as well. No need for a database trigger to enable them for example. Also, I have to admit that the documentation saying Outlines have been deprecated is starting to make me nervous about using them.
c) Looks like sometimes Profiles include IGNORE_OPTIM_EMBEDDED_HINTS and sometimes they don’t. My first thought was maybe the ignore hint was included only when there are embedded hints and it’s smart enough to leave the hint out when there aren’t any. But I found one that didn’t have any embedded hints that still had the ignore hint, so at this point it is unclear to me as to why sometimes it is there and not other times …
d) Yep, my tests showed that the plan can be changed by changing the hints as well, regardless of whether there are any statistical hints there or not. And 11g baselines look to me like enhanced Profiles. Although the Baselines have a plan_hash_value – so they can determine if they got the plan that generated the Baseline to begin with – a giant step forward in my opinion – although I wish they had just saved the plan itself and been done with it. It means they can throw out the whole plan and re-optimize (or pick another baseline), instead of just ignoring a single hint.
On the bug issue, if I understand you correctly you’re saying that creating an Outline also results in the buggy behavior (that being that the plan changes due to the invalid hint). I agree with that observation. Creating an Outline or a Profile in 10g or 11gR1 will change the plan due to this bug. Creating a Baseline in 11gR1 does not change the plan (even though the hint is still messed up) because Baselines are smart enough to know if the plan generated by the hints matches the plan that was originally used to generate the hints. And so, it throws the whole thing out and re-optimizes the statement from scratch.
Thanks for all the thoughtful comments.
Kerry
[…] regarding SQL Profiles (in a very nice collegial sort of way). You can see the original dialog here. One of his main points was that SQL Profiles were not meant to be a generic mechanism for forcing […]
Kerry,
Is it me or did the explain plain for the index-hinted query appear to have MUCH higher cost in terms of rows processed and CPU? Perhaps this is the reason the optimizer chose to ignore the profile.
Jon,
Once a Profile has been created, its hints will be applied (as long as they are valid, and they don’t contradict each other, etc…). Since this was a simple Profile with a single directive (INDEX_RS_ASC), when that hint was found to be invalid, it went back to the Full Table Scan. The reason the hint was invalid is because an index range scan cannot be done on an index where the leading columns are not part of the predicate. A skip scan or a full index scan are possible, but not a normal range scan. And since the IGNORE_OPTIM_EMBEDDED_HINTS hint is also included in the Profile, the inline hint in the text is ignored. So basically it’s back to picking a plan without any directive hints. At least that’s what I believe is happening.
Kerry
with, sql_hints.sql , have you ever seen this:
extractvalue(value(d), ‘/hint’) as outline_hints
*
ERROR at line 2:
ORA-00904: \D\: invalid identifier
?
Maybe I’m missing something obvious as I get a similar error with the COE script you posted recently.
Kyle,
I think you must have cut off the last d in the script. The last line should look like this:
) d;
If that’s not it, post a little more info (i.e. run the script with echo on and capture the output) and I’ll see if I can spot the problem. I haven’t had any issues with it on 10.1 – 11.2.
Kerry
[…] it was being ignored by the CBO. This is just the opposite of the one of the situations mentioned here by Kerry […]
Hi Kerry,
I have been using SQL hints on sql profiles with good degree of success. Recently, i am facing an issue where after applying the SQL hint (/*+INDEX(Table_ALias Index_Name)*/)to an SQL profile, the plan changes for certain child numbers only. For example, a sql_id had child numbers 0, 2, 4, 6 and dbms_xplan indicated that the child 2 has the hinted plan (good) whereas the others don’t. This is on Oracle 11.1.0.7.
I there a way to apply a sql profile to different child numbers of the same sql_id? Would appreciate if u can explore further?
Regards,
Naren
Naren,
SQL Profiles can apply to multiple SQL statements via the FORCE_MATCHING parameter, but there is no finer grained way of applying them than to a single SQL_ID. So no, there is no syntax for applying them to individual children. That would not help you anyway because as soon as you create a SQL Profile, the existing cursors will be invalidated and a new cursor will be created at the next execution. As to why you got the plan you wanted on one child and not others, I am not sure how you created your SQL Profile, maybe with my create_1_hint_sql_profile.sql script? At any rate, the syntax you are using “index (Table_Alias Index_Name)” would need to be modified to include a query block in order for it to work inside a SQL profile like this:
index(Table_Alias@Query_Block Index_Name)
The default query block name is SEL$1 for a simple query (you can see them by doing an XPLAN with ‘alias’)
select * from table(dbms_xplan.display_cursor(‘&sql_id’,’&child_no’,’alias’));
So the plan changing is probably a function of the optimizer just coming up with a different plan (maybe due to bind variable peeking).
Kerry
Very interesting and helpful article!
I have a query that is doing a full table scan. I ran the tuning optimizer task, and it recommended an SQL profile which I accepted, that promised 90%+ improvement. When comparing the plans, it proposed using an index. Good, so far!
Well, as in your case, when running the query, it still did the full table scan even though it said it used the profile.
The index hint for the profile used INDEX_RS_ASC. I believe that is the culprit.
When I run my query specifically with the /*+ INDEX (table_name my_index) */ hint, then the index is used.
If I run it with /*+ INDEX_RS_ASC (table_name my_index) */ then a full table scan is done.
Is there a way, without using your scripts to change the profile hint, for me to get Oracle to do use the INDEX hint rather than an INDEX_RS_ASC hint?
Thanks,
Bill
Hi Bill,
When trying to understand why a hint based mechanism is not doing what you expect, it’s best to look directly at the hints. You say you implemented a SQL profile suggested by the Tuning Advisor. Generally those hints look like OPT_ESTIMATE or statistics modification type hints. Have you run the sql_profile_hints.sql script to see exactly what’s being applied? If so can you paste the output here?
Kerry
Yes, I have. Here it is:
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@SEL$1 NAMES@SEL$1 UPPER_NAME_INDEX)
OUTLINE_LEAF(@SEL$1)
ALL_ROWS
OPT_PARAM(‘optimizer_index_caching’ 40)
OPT_PARAM(‘optimizer_index_cost_adj’ 40)
DB_VERSION(‘11.1.0.7’)
OPTIMIZER_FEATURES_ENABLE(‘11.1.0.7’)
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
Also – I used fix_sql_profile_hint.sql to change it to INDEX(@SEL$1 NAMES@SEL$1 UPPER_NAME_INDEX), and now I get the promised 90% improvement! However, it feels like I’m cheating (read “Oracle doesn’t support/recommend this”).
I’m hoping maybe I’m missing a setting or something that would cause the hint to be INDEX instead of INDEX_RS_ASC.
This looks like a profile created manually (by my create_sql_profile.sql script for example). At any rate, I don’t see any problem with what you’ve done. I recently found a reference on MOS to using dbms_sqltune.import_sql_profile to create manual profiles. So I think it is not something to worry about. See this post on that topic:
http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/
By the way, I’d be interested to know what access path the INDEX hint allowed that the INDEX_RS_ASC didn’t.
Kerry
Yes, the profile was created manually. I’m not sure why INDEX works, but INDEX_RS_ASC doesn’t. But, I can’t argue with my query that used to take 63 seconds, now takes 9 seconds. And, it sounds like Oracle is fine with manually created profiles, so I’m good to go. Thanks for the great info!
Bill
[…] (pt.1) 2. Do SQL Plan Baselines use hints (pt. 2) 3. SQL Plan Management – SQL Plan Baselines 4. Why isn’t Oracle using my outline/ SQL profile / SQL Plan Baseline 5. Dynamic Sampling and profiles/baselines 6. Licence differences between profiles and […]
Kerry,
In what scenarios are force_matching_signature=0? I created a sql tuning set of 30 statements to load into the SQL Management Base (dba_sql_plan_baselines) But, notice that not all of them get loaded into this table from the sql tuning set. I see the ones that had a value of 0 for the force_matching_signature column in the sql tuning set are the ones that didnt make it into the dba_sql_plan_baselines. Can you explain why these didnt make it to the dba_sql_plan_baselines?
Thanks,
Anu
[…] SQL profile requires a license. http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/ http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/ […]
[…] few statements they were not picked up on subsequent executions. So I used the process described here (specifically the create_sql_profile.sql script) to manually create a SQL Profile from these hints. […]
Kerry,
Thank you for your article…it is quite interesting and is helping me to learn alot. I realize this was posted a few years back and using 11.1.0.7, but we are seeing similar behaviour in our database and we are at 11.2.0.3. Was hoping you could shed some light…basically the problems is that the profile will work for a few days and the statement completes within seconds, then for some reason (we have an Oracle SR logged and possible bug fix coming) the optimizer will choose a poor performing plan and take hours to complete.
The statement is a MERGE and the sql_profile_hints11.sql output is below. Do you see anything in our output that points to the same problem you were seeing in 11.1.0.7? I’m a bit new to digging into the optimizer hints and am wondering if the presence of the INDEX_RS_xxxx hint is in itself and indication of a problem, especially if there is not a specific index listed. Also, I noticed with this query, that all of the INDEX hints specify column names rather than the actual index name… is this a new default for the 11.2.0.3 optimizer?
SQL > @sql_profile_hints11
Enter value for name: PROFILE_g2d5h97yjspgs
old 11: and p.name like nvl(‘&name’,name)
new 11: and p.name like nvl(‘PROFILE_g2d5h97yjspgs’,name)
HINT
——————————————————————————————————————————————————
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’)
DB_VERSION(‘11.2.0.3’)
ALL_ROWS
OUTLINE_LEAF(@”SEL$10″)
— multiple OUTLINE, OUTLINE_LEAF and MERGE hints removed to shorten a bit
NO_ACCESS(@”MRG$1″ “from$_subquery$_122″@”MRG$1″)
NO_ACCESS(@”MRG$1” “LATEST”@”MRG$1″)
FULL(@”MRG$1” “F”@”MRG$1″)
LEADING(@”MRG$1” “from$_subquery$_122″@”MRG$1” “LATEST”@”MRG$1” “F”@”MRG$1″)
USE_MERGE_CARTESIAN(@”MRG$1” “LATEST”@”MRG$1″)
USE_MERGE_CARTESIAN(@”MRG$1” “F”@”MRG$1″)
NO_ACCESS(@”SEL$4AE9668E” “L”@”SEL$29″)
INDEX_RS_ASC(@”SEL$4AE9668E” “F”@”SEL$2” (“FACT_INSTRUCTION”.”BALLOT_KEY” “FACT_INSTRUCTION”.”PROPOSAL_KEY”))
LEADING(@”SEL$4AE9668E” “L”@”SEL$29” “F”@”SEL$2″)
USE_NL(@”SEL$4AE9668E” “F”@”SEL$2″)
NO_ACCESS(@”SEL$70” “L”@”SEL$70″)
NO_ACCESS(@”SEL$C94288DC” “from$_subquery$_059″@”SEL$83″)
INDEX(@”SEL$C94288DC” “POL_PRBL”@”SEL$82” (“PROPOSAL_RECOMMENDATIONS_BL”.”PROPOSAL_ID” “PROPOSAL_RECOMMENDATIONS_BL”.”POLICY_ID”))
LEADING(@”SEL$C94288DC” “from$_subquery$_059″@”SEL$83” “POL_PRBL”@”SEL$82″)
USE_NL(@”SEL$C94288DC” “POL_PRBL”@”SEL$82″)
NO_ACCESS(@”SEL$3713694F” “PROPUNION”@”SEL$72″)
INDEX_RS_ASC(@”SEL$3713694F” “DMTG”@”SEL$71” (“DIM_MEETING”.”MEETING_KEY”))
INDEX_RS_ASC(@”SEL$3713694F” “DPR”@”SEL$78” (“DIM_PROPOSAL”.”MEETING_ID” “DIM_PROPOSAL”.”MEETING_VERSION_NBR” “DIM_PROPOSAL”.”PROPOSAL_ID”))
INDEX(@”SEL$3713694F” “BEN_PRBL”@”SEL$79” (“PROPOSAL_RECOMMENDATIONS_BL”.”PROPOSAL_ID” “PROPOSAL_RECOMMENDATIONS_BL”.”POLICY_ID”))
INDEX(@”SEL$3713694F” “DPOL”@”SEL$81” (“DIM_POLICY”.”POLICY_KEY”))
LEADING(@”SEL$3713694F” “PROPUNION”@”SEL$72” “DMTG”@”SEL$71” “DPR”@”SEL$78” “BEN_PRBL”@”SEL$79” “DPOL”@”SEL$81″)
USE_NL(@”SEL$3713694F” “DMTG”@”SEL$71″)
USE_NL(@”SEL$3713694F” “DPR”@”SEL$78″)
USE_NL(@”SEL$3713694F” “BEN_PRBL”@”SEL$79″)
USE_NL(@”SEL$3713694F” “DPOL”@”SEL$81″)
NLJ_BATCHING(@”SEL$3713694F” “DPOL”@”SEL$81″)
NO_ACCESS(@”SEL$7EEBD22F” “from$_subquery$_024″@”SEL$58″)
INDEX(@”SEL$7EEBD22F” “SVD”@”SEL$74” (“SPLIT_VOTE_DEFINITION_BL”.”SPLIT_VOTE_ID” “SPLIT_VOTE_DEFINITION_BL”.”SPLIT_VOTE_DESCRIPTION” “SPLIT_VOTE_DEFIN
ITION_BL”.”BALLOT_ID”))
INDEX(@”SEL$7EEBD22F” “DBAL”@”SEL$75” (“DIM_BALLOT”.”BALLOT_ID” “DIM_BALLOT”.”SPLIT_VOTE_ID”))
LEADING(@”SEL$7EEBD22F” “from$_subquery$_024″@”SEL$58” “SVD”@”SEL$74” “DBAL”@”SEL$75″)
USE_NL(@”SEL$7EEBD22F” “SVD”@”SEL$74″)
USE_NL(@”SEL$7EEBD22F” “DBAL”@”SEL$75″)
NLJ_BATCHING(@”SEL$7EEBD22F” “DBAL”@”SEL$75″)
NO_ACCESS(@”SEL$001B1E23” “from$_subquery$_004″@”SEL$44″)
INDEX(@”SEL$001B1E23” “DBAL”@”SEL$73” (“DIM_BALLOT”.”LB_LOGICAL_BALLOT_ID”))
LEADING(@”SEL$001B1E23″ “from$_subquery$_004″@”SEL$44” “DBAL”@”SEL$73″)
USE_NL(@”SEL$001B1E23” “DBAL”@”SEL$73″)
NLJ_BATCHING(@”SEL$001B1E23” “DBAL”@”SEL$73″)
INDEX_FFS(@”SEL$7CD094F0” “PROPOSAL_RECOMMENDATIONS_TCT”@”SEL$57” (“PROPOSAL_RECOMMENDATIONS_TCT”.”SUBSET_ID” “PROPOSAL_RECOMMENDATIONS_TCT”.”PROPOSAL
_ID” “PROPOSAL_RECOMMENDATIONS_TCT”.”POLICY_ID”))
INDEX(@”SEL$7CD094F0″ “BL”@”SEL$56” (“LOGICAL_PROPOSAL_BL”.”PROPOSAL_ID”))
LEADING(@”SEL$7CD094F0″ “PROPOSAL_RECOMMENDATIONS_TCT”@”SEL$57” “BL”@”SEL$56″)
USE_NL(@”SEL$7CD094F0” “BL”@”SEL$56″)
NLJ_BATCHING(@”SEL$7CD094F0” “BL”@”SEL$56″)
INDEX_FFS(@”SEL$178115D1” “T”@”SEL$50” (“PROPOSAL_TCT”.”SUBSET_ID” “PROPOSAL_TCT”.”PROPOSAL_ID”))
INDEX(@”SEL$178115D1″ “BL”@”SEL$50” (“LOGICAL_PROPOSAL_BL”.”PROPOSAL_ID”))
LEADING(@”SEL$178115D1″ “T”@”SEL$50” “BL”@”SEL$50″)
USE_NL(@”SEL$178115D1” “BL”@”SEL$50″)
NLJ_BATCHING(@”SEL$178115D1” “BL”@”SEL$50″)
INDEX(@”SEL$9ACD765A” “T”@”SEL$47” (“POLICY_TCT”.”SUBSET_ID” “POLICY_TCT”.”POLICY_ID”))
INDEX_RS_ASC(@”SEL$9ACD765A” “D”@”SEL$47” (“DIM_POLICY”.”POLICY_ID”))
INDEX_RS_ASC(@”SEL$9ACD765A” “DB”@”SEL$48” (“DIM_BALLOT”.”POLICY_KEY”))
INDEX(@”SEL$9ACD765A” “LP”@”SEL$49” (“LOGICAL_PROPOSAL_BL”.”LOGICAL_BALLOT_ID”))
LEADING(@”SEL$9ACD765A” “T”@”SEL$47” “D”@”SEL$47” “DB”@”SEL$48” “LP”@”SEL$49″)
USE_NL(@”SEL$9ACD765A” “D”@”SEL$47″)
USE_NL(@”SEL$9ACD765A” “DB”@”SEL$48″)
USE_NL(@”SEL$9ACD765A” “LP”@”SEL$49″)
NLJ_BATCHING(@”SEL$9ACD765A” “LP”@”SEL$49″)
INDEX_FFS(@”SEL$46404C7A” “T”@”SEL$46” (“LOGICAL_BALLOT_TCT”.”SUBSET_ID” “LOGICAL_BALLOT_TCT”.”LOGICAL_BALLOT_ID”))
INDEX(@”SEL$46404C7A” “LP”@”SEL$46” (“LOGICAL_PROPOSAL_BL”.”LOGICAL_BALLOT_ID”))
LEADING(@”SEL$46404C7A” “T”@”SEL$46” “LP”@”SEL$46″)
USE_NL(@”SEL$46404C7A” “LP”@”SEL$46″)
NLJ_BATCHING(@”SEL$46404C7A” “LP”@”SEL$46″)
INDEX_FFS(@”SEL$97C956A3” “T”@”SEL$45” (“BALLOT_TCT”.”SUBSET_ID” “BALLOT_TCT”.”LOGICAL_BALLOT_ID”))
INDEX(@”SEL$97C956A3″ “LP”@”SEL$45” (“LOGICAL_PROPOSAL_BL”.”LOGICAL_BALLOT_ID”))
LEADING(@”SEL$97C956A3″ “T”@”SEL$45” “LP”@”SEL$45″)
USE_NL(@”SEL$97C956A3” “LP”@”SEL$45″)
NLJ_BATCHING(@”SEL$97C956A3” “LP”@”SEL$45″)
INDEX_RS_ASC(@”SEL$51” “LOGICAL_PROPOSAL_TCT”@”SEL$51” (“LOGICAL_PROPOSAL_TCT”.”SUBSET_ID”))
INDEX_FFS(@”SEL$09EBE8F0″ “PROPOSAL_RECOMMENDATIONS_TCT”@”SEL$69” (“PROPOSAL_RECOMMENDATIONS_TCT”.”SUBSET_ID” “PROPOSAL_RECOMMENDATIONS_TCT”.”PROPOSAL
_ID” “PROPOSAL_RECOMMENDATIONS_TCT”.”POLICY_ID”))
INDEX(@”SEL$09EBE8F0″ “BL”@”SEL$68” (“SPLIT_VOTE_INSTRUCTION_BL”.”PROPOSAL_ID”))
LEADING(@”SEL$09EBE8F0″ “PROPOSAL_RECOMMENDATIONS_TCT”@”SEL$69” “BL”@”SEL$68″)
USE_NL(@”SEL$09EBE8F0” “BL”@”SEL$68″)
NLJ_BATCHING(@”SEL$09EBE8F0” “BL”@”SEL$68″)
INDEX_FFS(@”SEL$FDD5A144” “T”@”SEL$63” (“PROPOSAL_TCT”.”SUBSET_ID” “PROPOSAL_TCT”.”PROPOSAL_ID”))
INDEX(@”SEL$FDD5A144″ “BL”@”SEL$63” (“SPLIT_VOTE_INSTRUCTION_BL”.”PROPOSAL_ID”))
LEADING(@”SEL$FDD5A144″ “T”@”SEL$63” “BL”@”SEL$63″)
USE_NL(@”SEL$FDD5A144” “BL”@”SEL$63″)
NLJ_BATCHING(@”SEL$FDD5A144” “BL”@”SEL$63″)
INDEX(@”SEL$FF8D7C95” “T”@”SEL$60” (“POLICY_TCT”.”SUBSET_ID” “POLICY_TCT”.”POLICY_ID”))
INDEX(@”SEL$FF8D7C95″ “D”@”SEL$60” (“DIM_POLICY”.”POLICY_ID”))
FULL(@”SEL$FF8D7C95″ “LP”@”SEL$62″)
INDEX(@”SEL$FF8D7C95” “DB”@”SEL$61” (“DIM_BALLOT”.”SPLIT_VOTE_ID”))
LEADING(@”SEL$FF8D7C95″ “T”@”SEL$60” “D”@”SEL$60” “LP”@”SEL$62” “DB”@”SEL$61″)
USE_NL(@”SEL$FF8D7C95” “D”@”SEL$60″)
NLJ_BATCHING(@”SEL$FF8D7C95” “D”@”SEL$60″)
USE_MERGE_CARTESIAN(@”SEL$FF8D7C95” “LP”@”SEL$62″)
USE_NL(@”SEL$FF8D7C95” “DB”@”SEL$61″)
NLJ_BATCHING(@”SEL$FF8D7C95” “DB”@”SEL$61″)
INDEX_RS_ASC(@”SEL$5D0868CA” “T”@”SEL$59” (“SPLIT_VOTE_DEFINITION_TCT”.”SUBSET_ID” “SPLIT_VOTE_DEFINITION_TCT”.”BALLOT_ID”))
INDEX(@”SEL$5D0868CA” “SVI”@”SEL$59” (“SPLIT_VOTE_INSTRUCTION_BL”.”SPLIT_VOTE_ID”))
LEADING(@”SEL$5D0868CA” “T”@”SEL$59” “SVI”@”SEL$59″)
USE_NL(@”SEL$5D0868CA” “SVI”@”SEL$59″)
NLJ_BATCHING(@”SEL$5D0868CA” “SVI”@”SEL$59″)
INDEX_RS_ASC(@”SEL$64” “SPLIT_VOTE_INSTRUCTION_TCT”@”SEL$64” (“SPLIT_VOTE_INSTRUCTION_TCT”.”SUBSET_ID” “SPLIT_VOTE_INSTRUCTION_TCT”.”SPLIT_VOTE_ID” “S
PLIT_VOTE_INSTRUCTION_TCT”.”PROPOSAL_ID”))
NO_ACCESS(@”SEL$EC6400EA” “from$_subquery$_118″@”SEL$42″)
INDEX_RS_ASC(@”SEL$EC6400EA” “POL_PRBL”@”SEL$41” (“PROPOSAL_RECOMMENDATIONS_BL”.”PROPOSAL_ID” “PROPOSAL_RECOMMENDATIONS_BL”.”POLICY_ID”))
LEADING(@”SEL$EC6400EA” “from$_subquery$_118″@”SEL$42” “POL_PRBL”@”SEL$41″)
USE_NL(@”SEL$EC6400EA” “POL_PRBL”@”SEL$41″)
NO_ACCESS(@”SEL$D656D8CF” “PROPUNION”@”SEL$31″)
INDEX_RS_ASC(@”SEL$D656D8CF” “DMTG”@”SEL$30” (“DIM_MEETING”.”MEETING_KEY”))
INDEX_RS_ASC(@”SEL$D656D8CF” “DPR”@”SEL$37” (“DIM_PROPOSAL”.”MEETING_ID” “DIM_PROPOSAL”.”MEETING_VERSION_NBR” “DIM_PROPOSAL”.”PROPOSAL_ID”))
INDEX_RS_ASC(@”SEL$D656D8CF” “DPOL”@”SEL$40” (“DIM_POLICY”.”POLICY_KEY”))
INDEX_RS_ASC(@”SEL$D656D8CF” “BEN_PRBL”@”SEL$38” (“PROPOSAL_RECOMMENDATIONS_BL”.”PROPOSAL_ID” “PROPOSAL_RECOMMENDATIONS_BL”.”POLICY_ID”))
LEADING(@”SEL$D656D8CF” “PROPUNION”@”SEL$31” “DMTG”@”SEL$30” “DPR”@”SEL$37” “DPOL”@”SEL$40” “BEN_PRBL”@”SEL$38″)
USE_NL(@”SEL$D656D8CF” “DMTG”@”SEL$30″)
USE_NL(@”SEL$D656D8CF” “DPR”@”SEL$37″)
USE_NL(@”SEL$D656D8CF” “DPOL”@”SEL$40″)
USE_NL(@”SEL$D656D8CF” “BEN_PRBL”@”SEL$38″)
NO_ACCESS(@”SEL$623E50FE” “from$_subquery$_083″@”SEL$17″)
INDEX(@”SEL$623E50FE” “SVD”@”SEL$33” (“SPLIT_VOTE_DEFINITION_BL”.”SPLIT_VOTE_ID” “SPLIT_VOTE_DEFINITION_BL”.”SPLIT_VOTE_DESCRIPTION” “SPLIT_VOTE_DEFIN
ITION_BL”.”BALLOT_ID”))
INDEX(@”SEL$623E50FE” “DBAL”@”SEL$34” (“DIM_BALLOT”.”BALLOT_ID” “DIM_BALLOT”.”SPLIT_VOTE_ID”))
LEADING(@”SEL$623E50FE” “from$_subquery$_083″@”SEL$17” “SVD”@”SEL$33” “DBAL”@”SEL$34″)
USE_NL(@”SEL$623E50FE” “SVD”@”SEL$33″)
USE_NL(@”SEL$623E50FE” “DBAL”@”SEL$34″)
NLJ_BATCHING(@”SEL$623E50FE” “DBAL”@”SEL$34″)
NO_ACCESS(@”SEL$27E25EF9” “from$_subquery$_063″@”SEL$3″)
INDEX(@”SEL$27E25EF9” “DBAL”@”SEL$32” (“DIM_BALLOT”.”LB_LOGICAL_BALLOT_ID”))
LEADING(@”SEL$27E25EF9″ “from$_subquery$_063″@”SEL$3” “DBAL”@”SEL$32″)
USE_NL(@”SEL$27E25EF9” “DBAL”@”SEL$32″)
NLJ_BATCHING(@”SEL$27E25EF9” “DBAL”@”SEL$32″)
INDEX_FFS(@”SEL$81B4A55D” “PROPOSAL_RECOMMENDATIONS_TCT”@”SEL$16” (“PROPOSAL_RECOMMENDATIONS_TCT”.”SUBSET_ID” “PROPOSAL_RECOMMENDATIONS_TCT”.”PROPOSAL
_ID” “PROPOSAL_RECOMMENDATIONS_TCT”.”POLICY_ID”))
INDEX(@”SEL$81B4A55D” “BL”@”SEL$15” (“LOGICAL_PROPOSAL_BL”.”PROPOSAL_ID”))
LEADING(@”SEL$81B4A55D” “PROPOSAL_RECOMMENDATIONS_TCT”@”SEL$16” “BL”@”SEL$15″)
USE_NL(@”SEL$81B4A55D” “BL”@”SEL$15″)
NLJ_BATCHING(@”SEL$81B4A55D” “BL”@”SEL$15″)
INDEX_FFS(@”SEL$F75B9594” “T”@”SEL$9” (“PROPOSAL_TCT”.”SUBSET_ID” “PROPOSAL_TCT”.”PROPOSAL_ID”))
INDEX(@”SEL$F75B9594″ “BL”@”SEL$9” (“LOGICAL_PROPOSAL_BL”.”PROPOSAL_ID”))
LEADING(@”SEL$F75B9594″ “T”@”SEL$9” “BL”@”SEL$9″)
USE_NL(@”SEL$F75B9594” “BL”@”SEL$9″)
NLJ_BATCHING(@”SEL$F75B9594” “BL”@”SEL$9″)
INDEX(@”SEL$A797C8E2” “T”@”SEL$6” (“POLICY_TCT”.”SUBSET_ID” “POLICY_TCT”.”POLICY_ID”))
INDEX_RS_ASC(@”SEL$A797C8E2″ “D”@”SEL$6” (“DIM_POLICY”.”POLICY_ID”))
INDEX_RS_ASC(@”SEL$A797C8E2″ “DB”@”SEL$7” (“DIM_BALLOT”.”POLICY_KEY”))
INDEX(@”SEL$A797C8E2″ “LP”@”SEL$8” (“LOGICAL_PROPOSAL_BL”.”LOGICAL_BALLOT_ID”))
LEADING(@”SEL$A797C8E2″ “T”@”SEL$6” “D”@”SEL$6” “DB”@”SEL$7” “LP”@”SEL$8″)
USE_NL(@”SEL$A797C8E2” “D”@”SEL$6″)
USE_NL(@”SEL$A797C8E2” “DB”@”SEL$7″)
USE_NL(@”SEL$A797C8E2” “LP”@”SEL$8″)
NLJ_BATCHING(@”SEL$A797C8E2” “LP”@”SEL$8″)
INDEX_FFS(@”SEL$C1062F3C” “T”@”SEL$5” (“LOGICAL_BALLOT_TCT”.”SUBSET_ID” “LOGICAL_BALLOT_TCT”.”LOGICAL_BALLOT_ID”))
INDEX(@”SEL$C1062F3C” “LP”@”SEL$5” (“LOGICAL_PROPOSAL_BL”.”LOGICAL_BALLOT_ID”))
LEADING(@”SEL$C1062F3C” “T”@”SEL$5” “LP”@”SEL$5″)
USE_NL(@”SEL$C1062F3C” “LP”@”SEL$5″)
NLJ_BATCHING(@”SEL$C1062F3C” “LP”@”SEL$5″)
INDEX_FFS(@”SEL$D456AC30” “T”@”SEL$4” (“BALLOT_TCT”.”SUBSET_ID” “BALLOT_TCT”.”LOGICAL_BALLOT_ID”))
INDEX(@”SEL$D456AC30″ “LP”@”SEL$4” (“LOGICAL_PROPOSAL_BL”.”LOGICAL_BALLOT_ID”))
LEADING(@”SEL$D456AC30″ “T”@”SEL$4” “LP”@”SEL$4″)
USE_NL(@”SEL$D456AC30” “LP”@”SEL$4″)
NLJ_BATCHING(@”SEL$D456AC30” “LP”@”SEL$4″)
INDEX_RS_ASC(@”SEL$10” “LOGICAL_PROPOSAL_TCT”@”SEL$10” (“LOGICAL_PROPOSAL_TCT”.”SUBSET_ID”))
INDEX_FFS(@”SEL$5AEE3A48″ “PROPOSAL_RECOMMENDATIONS_TCT”@”SEL$28” (“PROPOSAL_RECOMMENDATIONS_TCT”.”SUBSET_ID” “PROPOSAL_RECOMMENDATIONS_TCT”.”PROPOSAL
_ID” “PROPOSAL_RECOMMENDATIONS_TCT”.”POLICY_ID”))
INDEX(@”SEL$5AEE3A48″ “BL”@”SEL$27” (“SPLIT_VOTE_INSTRUCTION_BL”.”PROPOSAL_ID”))
LEADING(@”SEL$5AEE3A48″ “PROPOSAL_RECOMMENDATIONS_TCT”@”SEL$28” “BL”@”SEL$27″)
USE_NL(@”SEL$5AEE3A48” “BL”@”SEL$27″)
NLJ_BATCHING(@”SEL$5AEE3A48” “BL”@”SEL$27″)
INDEX_FFS(@”SEL$E561C613” “T”@”SEL$22” (“PROPOSAL_TCT”.”SUBSET_ID” “PROPOSAL_TCT”.”PROPOSAL_ID”))
INDEX(@”SEL$E561C613″ “BL”@”SEL$22” (“SPLIT_VOTE_INSTRUCTION_BL”.”PROPOSAL_ID”))
LEADING(@”SEL$E561C613″ “T”@”SEL$22” “BL”@”SEL$22″)
USE_NL(@”SEL$E561C613” “BL”@”SEL$22″)
NLJ_BATCHING(@”SEL$E561C613” “BL”@”SEL$22″)
INDEX(@”SEL$FA292B57” “T”@”SEL$19” (“POLICY_TCT”.”SUBSET_ID” “POLICY_TCT”.”POLICY_ID”))
INDEX(@”SEL$FA292B57″ “D”@”SEL$19” (“DIM_POLICY”.”POLICY_ID”))
FULL(@”SEL$FA292B57″ “LP”@”SEL$21″)
INDEX(@”SEL$FA292B57” “DB”@”SEL$20” (“DIM_BALLOT”.”SPLIT_VOTE_ID”))
LEADING(@”SEL$FA292B57″ “T”@”SEL$19” “D”@”SEL$19” “LP”@”SEL$21” “DB”@”SEL$20″)
USE_NL(@”SEL$FA292B57” “D”@”SEL$19″)
NLJ_BATCHING(@”SEL$FA292B57” “D”@”SEL$19″)
USE_MERGE_CARTESIAN(@”SEL$FA292B57” “LP”@”SEL$21″)
USE_NL(@”SEL$FA292B57” “DB”@”SEL$20″)
NLJ_BATCHING(@”SEL$FA292B57” “DB”@”SEL$20″)
INDEX_RS_ASC(@”SEL$54C05AD7” “T”@”SEL$18” (“SPLIT_VOTE_DEFINITION_TCT”.”SUBSET_ID” “SPLIT_VOTE_DEFINITION_TCT”.”BALLOT_ID”))
INDEX(@”SEL$54C05AD7″ “SVI”@”SEL$18” (“SPLIT_VOTE_INSTRUCTION_BL”.”SPLIT_VOTE_ID”))
LEADING(@”SEL$54C05AD7″ “T”@”SEL$18” “SVI”@”SEL$18″)
USE_NL(@”SEL$54C05AD7” “SVI”@”SEL$18″)
NLJ_BATCHING(@”SEL$54C05AD7” “SVI”@”SEL$18″)
INDEX_RS_ASC(@”SEL$23” “SPLIT_VOTE_INSTRUCTION_TCT”@”SEL$23” (“SPLIT_VOTE_INSTRUCTION_TCT”.”SUBSET_ID” “SPLIT_VOTE_INSTRUCTION_TCT”.”SPLIT_VOTE_ID” “S
PLIT_VOTE_INSTRUCTION_TCT”.”PROPOSAL_ID”))
Sorry, but I have the same pb like Kyle Hailey
SQL> @create_sql_profile.sql
Enter value for sql_id: cxwq5xjsm7n3g
Enter value for child_no: 0
Enter value for category:
Enter value for force_matching: TRUE
extractvalue(value(d), ‘/hint’) as outline_hints
*
ERROR at line 6:
ORA-06550: line 6, column 20:
PL/SQL: ORA-00904: “D”: invalid identifier
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
I checked before the script and I have the )) d; at the last of my statement.
Hi NourBEE,
The only way I’ve been able to reproduce that error is to not have the d at the end of the statement. Can you set echo on and run the script and post the output here so I can see what the statement looks like?
Also, I have since rewritten that script so as not to depend on the rg_sqlprof1.sql script. Here’s a link to the new version of the create_sql_profile.sql script.
Kerry
Hi joeB,
Sorry for the delay in replying to your comment. As you know, SQL Profiles are just a collection of hints that “try” to influence the optimizer enough to limit it to a single plan. In complex queries, the set of hints may not be complete enough to limit the choices to a single plan, Also something in the environment could change that makes a hint invalid (dropped index for example). The new column based INDEX hint syntax also allows more flexibility for the optimizer to pick different plans. You could try using my pif.sql statement to transform the INDEX hints into the more specific form which uses the index name to get a more restrictive SQL Profile. See this post for more info: Fixing Bad Index Hints in SQL Profiles (automatically)
Kerry
hi kerry,
Seems like i am having the same problem as kyle and NourBEE…wierd part is when i run it on my other laptop(11.2.0.3) it works perfectly fine…the db i am having issue on is 10.2.0.1.0 (linux, 32 bit)
sys@10gdb> set echo on;
sys@10gdb> @sql_hint
sys@10gdb> — Usage: This scripts prompts for two values.
sys@10gdb> —
sys@10gdb> — sql_id: the sql_id of the statement (must be in the shared pool)
sys@10gdb> —
sys@10gdb> — child_no: the child_no of the statement from v$sql
sys@10gdb> —
sys@10gdb> —
sys@10gdb> — Description:
sys@10gdb> —
sys@10gdb> — Pulls Outline Hints from the OTHER_XML field of V$SQL_PLAN.
sys@10gdb> —
sys@10gdb> —————————————————————————————
sys@10gdb> select
2 extractvalue(value(d), ‘/hint’) as outline_hints
3 from
4 xmltable(‘/*/outline_data/hint’
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 v$sql_plan
10 where
11 sql_id like nvl(‘&sql_id’,sql_id)
12 and child_number = &child_no
13 and other_xml is not null
14 )
15 ) d;
Enter value for sql_id: 5vy5qjd3fsn5c
Enter value for child_no: 0
extractvalue(value(d), ‘/hint’) as outline_hints
*
ERROR at line 2:
ORA-00904: “D”: invalid identifier
Elapsed: 00:00:00.00
also the same when trying to create a profile…
sys@10gdb> set echo on;
sys@10gdb> @CREATE_SQL_PROFILE
sys@10gdb> accept sql_id –
> prompt ‘Enter value for sql_id: ‘ –
> default ‘X0X0X0X0’
Enter value for sql_id: 5vy5qjd3fsn5c
sys@10gdb> accept child_no –
> prompt ‘Enter value for child_no: ‘
Enter value for child_no: 0
sys@10gdb> accept category –
> prompt ‘Enter value for category: ‘ –
> default ‘DEFAULT’
Enter value for category:
sys@10gdb> accept force_matching –
> prompt ‘Enter value for force_matching: ‘ –
> default ‘false’
Enter value for force_matching:
sys@10gdb>
sys@10gdb> @rg_sqlprof1 ‘&sql_id’ &child_no ‘&category’ ‘&force_matching’
sys@10gdb> /* Randolf Giest */
sys@10gdb> — creates a sql profile from shared pool
sys@10gdb> — sql_id child_no category force_matching
sys@10gdb> declare
2 ar_profile_hints sys.sqlprof_attr;
3 cl_sql_text clob;
4 begin
5 select
6 extractvalue(value(d), ‘/hint’) as outline_hints
7 bulk collect
8 into
9 ar_profile_hints
10 from
11 xmltable(‘/*/outline_data/hint’
12 passing (
13 select
14 xmltype(other_xml) as xmlval
15 from
16 v$sql_plan
17 where
18 sql_id = ‘&&1’
19 and child_number = &&2
20 and other_xml is not null
21 )
22 ) d;
23
24 select
25 sql_fulltext
26 into
27 cl_sql_text
28 from
29 v$sql
30 where
31 sql_id = ‘&&1’
32 and child_number = &&2;
33
34 dbms_sqltune.import_sql_profile(
35 sql_text => cl_sql_text
36 , profile => ar_profile_hints
37 , category => ‘&&3’
38 , name => ‘PROFILE_&&1’
39 — use force_match => true
40 — to use CURSOR_SHARING=SIMILAR
41 — behaviour, i.e. match even with
42 — differing literals
43 , force_match => &&4
44 );
45 end;
46 /
extractvalue(value(d), ‘/hint’) as outline_hints
*
ERROR at line 6:
ORA-06550: line 6, column 20:
PL/SQL: ORA-00904: “D”: invalid identifier
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
Elapsed: 00:00:00.00
sys@10gdb> undef sql_id
sys@10gdb> undef child_no
sys@10gdb> undef category
sys@10gdb> undef force_matching
sys@10gdb>
Sorry Max,
I can’t seem to reproduce that error. I do not have access to a 10.2.0.1 database but am suspecting there is an issue with the xml syntax on that version (although I tested it on 10.1 at one point). Anyway, you might try changing up the query to a different syntax, even using substring or something. You know where the data is now. 🙂
Kerry
Hi Kerry,
I tried playing around with it, but didnt seem to work… a little help would be appreciated. I am a sysadmin by day and a DBA at night so do not have very deep knowledge of sql,pl/sql
Hi Max,
I suspect it is a version specific issue but I don’t have a 10.2.0.1 instance to play with. I’m sure the xml query could be re-written to work but am unable to help at the moment.
Kerry
Thanks Kerry, i understand you do not have access to 10.2.0.1 But here is the one that Jonathan Lewis helped me with….This is for other people who might need it, as unfortunately quite a few ppl still on the old version….
select
extractvalue(value(t),’.’) hint
from
table(
select
xmlsequence(
extract(xmltype(other_xml),’/other_xml/outline_data/hint’)
)
from
v$sql_plan
where
sql_id = ‘&m_sql_id’
and child_number = &m_child_no
and other_xml is not null
) t
/
great articles..
we noticed same issue..created sql profile from sql tuning advisor but after creating, query using it but plan still old one without change.
so what about to take good plan which generated via index and put in sql plan baseline instead of working with sql profile.
like “Worked Example of Replacing the Current Existing Plan with a Different Plan Using a SPM SQL Plan Baseline (Doc ID 1580762.1)”